package com.F01.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import org.apache.log4j.Logger;

import com.F01.actionForm.F010101ActionForm;
import com.F01.actionForm.F010102ActionForm;
import com.config.Constants;
import com.dao.common.DbOperationExt;
import com.dbtools.Dispose;
import com.exception.SelectException;
import com.info.T_S_YHCZXXinfo;
import com.util.CommonTools;

/**
 * @wangk071226
 * @订单信息管理
 * @数据库操作类
 * @方法命名规则：静态参数名称、例如：查询方法，方法名 QUERYLIST();
 * @数据库某字段如果的新增值，如果要取当前时间，请在数据库中表时间的字段上设置GETDATE(), @ insert语句执行时，会自动插入时间。避免系统时间于服务器时间不统一造成错误。 @ 例表c_customers的customer_date字段。
 */
public class F010102DAO extends DbOperationExt {

	/**
	 * 记录操作日志;
	 */
	Logger log = Logger.getLogger(F010102DAO.class);

	/**
	 * 构造方法获取数据源;
	 * 
	 */
	public F010102DAO() {
		super();
	}

	public F010102DAO(Connection conn) {
		super(conn);
	}

	/**
	 * @查询
	 */
	public List QUERYLIST(F010102ActionForm actionForm,
			HttpServletRequest request) {
		StringBuffer msql = new StringBuffer();
		StringBuffer condition = new StringBuffer();
		/** 判断查询条件，拼装查询SQL */
		/** 订单号码 */
		if (actionForm.getCotract_number() != null
				&& !actionForm.getCotract_number().equals("")) {
			condition.append(" and cotract_number = '"
					+ actionForm.getCotract_number().trim() + "' ");
		}
		/** 客户名称 */
		if (actionForm.getCustomer_code() != null
				&& !actionForm.getCustomer_code().equals("")) {
			condition.append(" and customer_code = '"
					+ actionForm.getCustomer_code().trim() + "' ");
		}
		/** 客户类别 */
		if (actionForm.getCustomer_type() != null
				&& !actionForm.getCustomer_type().equals("")) {
			condition.append(" and customer_type = '"
					+ actionForm.getCustomer_type().trim() + "' ");
		}
		/** 工 作 号 */
		if (actionForm.getManufacturing_order() != null
				&& !actionForm.getManufacturing_order().equals("")) {
			condition.append(" and manufacturing_order = '"
					+ actionForm.getManufacturing_order().trim() + "' ");
		}
		/** 产品类别 */
		if (actionForm.getProduct_type() != null
				&& !actionForm.getProduct_type().equals("")) {
			condition.append(" and product_type = '"
					+ actionForm.getProduct_type().trim() + "' ");
		}
		/** 产品型号 */
		if (actionForm.getModel() != null && !actionForm.getModel().equals("")) {
			condition.append(" and model = '" + actionForm.getModel().trim()
					+ "' ");
		}
		/** 是否担保 */
		if (actionForm.getBank_guaranty() != null
				&& !actionForm.getBank_guaranty().equals("")) {
			condition.append(" and bank_guaranty = '"
					+ actionForm.getBank_guaranty().trim() + "' ");
		}
		/** 签订时间 */
		if ((actionForm.getSignaure_date_begin() != null && !""
				.equals(actionForm.getSignaure_date_begin()))
				|| (actionForm.getSignaure_date_end() != null && !""
						.equals(actionForm.getSignaure_date_end()))) {
			if ((actionForm.getSignaure_date_begin() != null
					&& !"".equals(actionForm.getSignaure_date_begin())
					&& (actionForm.getSignaure_date_end()) != null && !""
					.equals(actionForm.getSignaure_date_end()))) {
				condition.append(" and signaure_date BETWEEN '"
						+ actionForm.getSignaure_date_begin().trim() + "'");
				condition.append(" and '"
						+ actionForm.getSignaure_date_end().trim() + "' ");
			}
			if (!"".equals(actionForm.getSignaure_date_begin())
					&& "".equals(actionForm.getSignaure_date_end())) {
				condition.append(" and signaure_date >='"
						+ actionForm.getSignaure_date_begin().trim() + "'");
			}
			if ("".equals(actionForm.getSignaure_date_begin())
					&& !"".equals(actionForm.getSignaure_date_end())) {
				condition.append(" and signaure_date <= '"
						+ actionForm.getSignaure_date_end().trim() + "'");
			}
		}
		/** 交货时间起 */
		if ((actionForm.getCustomer_delivery_date_begin() != null && !""
				.equals(actionForm.getCustomer_delivery_date_begin()))
				|| (actionForm.getCustomer_delivery_date_end() != null && !""
						.equals(actionForm.getCustomer_delivery_date_end()))) {

			if (!"".equals(actionForm.getCustomer_delivery_date_begin())
					&& !"".equals(actionForm.getCustomer_delivery_date_end())) {
				condition.append(" and customer_delivery_date BETWEEN '"
						+ actionForm.getCustomer_delivery_date_begin().trim()
						+ "'");
				condition.append(" and '"
						+ actionForm.getCustomer_delivery_date_end().trim()
						+ "' ");
			}
			if (!"".equals(actionForm.getCustomer_delivery_date_begin())
					&& "".equals(actionForm.getCustomer_delivery_date_end())) {
				condition.append(" and customer_delivery_date >='"
						+ actionForm.getCustomer_delivery_date_begin().trim()
						+ "'");
			}
			if ("".equals(actionForm.getCustomer_delivery_date_begin())
					&& !"".equals(actionForm.getCustomer_delivery_date_end())) {
				condition.append(" and customer_delivery_date >='"
						+ actionForm.getCustomer_delivery_date_end() + "'");
			}
		}
		/** 订单状态 */
		if (actionForm.getModel() != null && !actionForm.getState().equals("")) {
			condition.append(" and state = '" + actionForm.getState().trim()
					+ "' ");
		}
		List list = new ArrayList();
		Map map = null;
		ResultSet rest = null;

		msql.append(" SELECT 																											");
		msql
				.append("   cotract_number,signaure_date,manufacturing_order,model,description,        ");
		msql
				.append("   quantity,VAT,unit_price,total_price, transport,commission,         ");
		msql.append("   customer_delivery_date,payment_terms, ");
		msql
				.append("   adyh.FUN_DECODE(bank_guaranty) as bank_guaranty,bond_date,bond_value,         ");
		msql
				.append("   adyh.FUN_C_CUSTOMERS(customer_code) as customer_code,area,saleman_code,passing_date,area_group,  ");
		msql
				.append("   adyh.FUN_DECODE(own_production) as own_production,adyh.FUN_T_PRODUCT_TYPE(product_type) as product_type,");
		msql
				.append("   adyh.FUN_C_CUSTOMER_TYPE(customer_type) as customer_type ,adyh.FUN_STATE_TYPE(state) as statename,state as state ");
		msql.append(" FROM c_orders 						                      ");
		msql.append(" where 1=1 " + condition.toString() + " and state <> '6'");

		msql.append(" order by cotract_number  ");
		try {
			log.info("订单信息管理 QUERYLIST sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				map = new HashMap();
				map.put("cotract_number", CommonTools.null2String(rest
						.getString("cotract_number")));
				map.put("signaure_date", CommonTools.null2String(CommonTools
						.getDateTime((rest.getDate("signaure_date")))));
				map.put("manufacturing_order", CommonTools.null2String(rest
						.getString("manufacturing_order")));
				map.put("model", CommonTools.null2String(rest
						.getString("model")));
				map.put("description", CommonTools.null2String(rest
						.getString("description")));
				map.put("quantity", CommonTools.null2String(rest
						.getString("quantity")));
				map.put("VAT", CommonTools.null2String(rest.getString("VAT")));
				map.put("unit_price", CommonTools.null2String(rest
						.getString("unit_price")));
				map.put("total_price", CommonTools.null2String(rest
						.getString("total_price")));
				map.put("transport", CommonTools.null2String(rest
						.getString("transport")));
				map.put("commission", CommonTools.null2String(rest
						.getString("commission")));
				map.put("customer_delivery_date", CommonTools
						.null2String(CommonTools.getDateTime((rest
								.getDate("customer_delivery_date")))));
				map.put("payment_terms", CommonTools.null2String(rest
						.getString("payment_terms")));
				map.put("bank_guaranty", CommonTools.null2String(rest
						.getString("bank_guaranty")));
				map.put("bond_date", CommonTools.null2String(rest
						.getString("bond_date")));
				map.put("bond_value", CommonTools.null2String(rest
						.getString("bond_value")));
				map.put("customer_code", CommonTools.null2String(rest
						.getString("customer_code")));
				map
						.put("area", CommonTools.null2String(rest
								.getString("area")));
				map.put("saleman_code", CommonTools.null2String(rest
						.getString("saleman_code")));
				map.put("passing_date", CommonTools.null2String(rest
						.getString("passing_date")));
				map.put("area_group", CommonTools.null2String(rest
						.getString("area_group")));
				map.put("own_production", CommonTools.null2String(rest
						.getString("own_production")));
				map.put("product_type", CommonTools.null2String(rest
						.getString("product_type")));
				map.put("customer_type", CommonTools.null2String(rest
						.getString("customer_type")));
				map.put("state", CommonTools.null2String(rest
						.getString("state")));
				map.put("statename", CommonTools.null2String(rest
						.getString("statename")));
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}

		return list;
	}

	/**
	 * @新增保存
	 * @要记录操作，在插入后要向操作记录表也插一条语句，也就是用执行多天SQL， @ 所有用super.executeUpdate(sqlList,true)执行
	 */
	public boolean SAVE_INSERT(F010102ActionForm actionForm,
			T_S_YHCZXXinfo t_YhczxxInfo) {
		boolean falg = false;
		List sqlList = new ArrayList();
		/** 存放SQL的LIST */
		StringBuffer yhczsql = new StringBuffer();
		/** 操作记录SQL */
		yhczsql = getInsZzxxSQL(t_YhczxxInfo);
		/** 获得操作记录SQL */
		StringBuffer msql = new StringBuffer();
		msql.append("INSERT INTO c_orders(			             ");
		msql.append("  cotract_number,                           ");
		msql.append("  signaure_date,                            ");
		msql.append("  manufacturing_order,                      ");
		msql.append("  model,                                    ");
		msql.append("  description,                              ");
		msql.append("  quantity,                                 ");
		msql.append("  VAT,                                      ");
		msql.append("  unit_price,                               ");
		msql.append("  total_price,                              ");
		msql.append("  transport,                                ");
		msql.append("  commission,                               ");
		msql.append("  customer_delivery_date,                   ");
		msql.append("  payment_terms,                            ");
		msql.append("  bank_guaranty,                            ");
		msql.append("  bond_date,                                ");
		msql.append("  bond_value,                               ");
		msql.append("  customer_code,                            ");
		msql.append("  area,                                     ");
		msql.append("  saleman_code,                             ");
		msql.append("  passing_date,                             ");
		msql.append("  area_group,                               ");
		msql.append("  own_production,                           ");
		msql.append("  remark,                                   ");
		msql.append("  customer_type,                            ");
		msql.append("  product_type,                             ");
		msql.append("  state                                     ");
		msql.append("  )                                         ");
		msql.append(" VALUES (                                   ");
		msql.append("'" + actionForm.getCotract_number() + "',");
		msql.append("'" + actionForm.getSignaure_date() + "',");
		msql.append("'" + actionForm.getManufacturing_order() + "',");
		msql.append("'" + actionForm.getModel() + "',");
		msql.append("'" + actionForm.getDescription() + "',");
		msql.append("'" + actionForm.getQuantity() + "',");
		msql.append("'" + actionForm.getVAT() + "',");
		msql.append("'" + actionForm.getUnit_price() + "',");
		msql.append("'" + actionForm.getTotal_price() + "',");
		msql.append("'" + actionForm.getTransport() + "',");
		msql.append("'" + actionForm.getCommission() + "',");
		msql.append("'" + actionForm.getCustomer_delivery_date() + "',");
		msql.append("'" + actionForm.getPayment_terms() + "',");
		msql.append("'" + actionForm.getBank_guaranty() + "',");
		msql.append("'" + actionForm.getBond_date() + "',");
		msql.append("'" + actionForm.getBond_value() + "',");
		msql.append("'" + actionForm.getCustomer_code() + "',");
		msql.append("'" + actionForm.getArea() + "',");
		msql.append("'" + actionForm.getSaleman_code() + "',");
		msql.append("'" + actionForm.getPassing_date() + "',");
		msql.append("'" + actionForm.getArea_group() + "',");
		msql.append("'" + actionForm.getOwn_production() + "',");
		msql.append("'" + actionForm.getRemark() + "',");
		msql.append("'" + actionForm.getCustomer_type() + "',");
		msql.append("'" + actionForm.getProduct_type() + "',");
		msql.append(" '1' ");
		msql.append("  )                                    ");

		try {
			log.info("订单信息管理 SAVE_INSERT sql is " + msql.toString());
			sqlList.add(msql);
			sqlList.add(yhczsql);
			falg = super.executeUpdate(sqlList, true);
			actionForm.setErrorFlag("0");
			/** 成功 */
		} catch (SQLException ex) {
			if ("1".equals(String.valueOf(ex.getErrorCode()))) {
				/** ex.getErrorCode()返回一，表示主键冲突 */
				actionForm.setErrorFlag("1");
				/** 主键冲突! */
			} else {
				actionForm.setErrorFlag("1");
				/** 操作失败 */
			}
		} catch (Exception e) {
			actionForm.setErrorFlag("3");
			/**  */
			e.printStackTrace();
			super.rollback();

		}
		super.commit();

		return falg;
	}

	/**
	 * @编辑
	 */
	public void EDIT(F010102ActionForm actionForm, HttpServletRequest request) {
		ResultSet rest = null;
		StringBuffer msql = new StringBuffer();

		msql.append(" SELECT 																											");
		msql
				.append("   cotract_number,signaure_date,manufacturing_order,         ");
		msql.append("   model,description,quantity,VAT,unit_price,         ");
		msql
				.append("   total_price, transport,commission,customer_delivery_date, ");
		msql
				.append("   payment_terms,bank_guaranty,bond_date,bond_value,         ");
		msql
				.append("   adyh.FUN_C_CUSTOMERS(customer_code) as customer_code,area,saleman_code,passing_date,area_group,  ");
		msql
				.append("   own_production,product_type,customer_type                 ");
		msql.append(" FROM c_orders 						                      ");
		msql.append(" where cotract_number='"
				+ CommonTools.null2String(request
						.getParameter("cotract_number").trim()) + "'");
		msql.append(" and manufacturing_order='"
				+ CommonTools.null2String(request.getParameter(
						"manufacturing_order").trim()) + "'");
		try {
			log.info("订单信息管理 QUERYLIST sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				actionForm.setCotract_number(CommonTools.null2String(rest
						.getString("cotract_number")));
				actionForm.setSignaure_date(CommonTools.null2String(CommonTools
						.getDateTime((rest.getDate("signaure_date")))));
				actionForm.setManufacturing_order(CommonTools.null2String(rest
						.getString("manufacturing_order")));
				actionForm.setOriginally_manufacturing_order(CommonTools
						.null2String(rest.getString("manufacturing_order")));
				actionForm.setOriginally_manufacturing_order(CommonTools
						.null2String(rest.getString("manufacturing_order")));
				actionForm.setModel(CommonTools.null2String(rest
						.getString("model")));
				actionForm.setDescription(CommonTools.null2String(rest
						.getString("description")));
				actionForm.setQuantity(CommonTools.null2String(rest
						.getString("quantity")));
				actionForm.setVAT(CommonTools
						.null2String(rest.getString("VAT")));
				actionForm.setUnit_price(CommonTools.null2String(rest
						.getString("unit_price")));
				actionForm.setTotal_price(CommonTools.null2String(rest
						.getString("total_price")));
				actionForm.setTransport(CommonTools.null2String(rest
						.getString("transport")));
				actionForm.setCommission(CommonTools.null2String(rest
						.getString("commission")));
				actionForm.setCustomer_delivery_date(CommonTools
						.null2String(CommonTools.getDateTime((rest
								.getDate("customer_delivery_date")))));
				actionForm.setPayment_terms(CommonTools.null2String(rest
						.getString("payment_terms")));
				String bank_guaranty = CommonTools.null2String(rest
						.getString("bank_guaranty"));
				actionForm.setBank_guaranty(bank_guaranty);
				actionForm.setFalg(bank_guaranty);
				String bond_date = CommonTools.null2String(CommonTools
						.getDateTime((rest.getDate("bond_date"))));
				log.info("bond_date =" + bond_date);
				if ("1900-01-01".equals(bond_date)) {
					bond_date = "";
				}
				log.info("bond_date =" + bond_date);
				actionForm.setBond_date(bond_date);
				actionForm.setBond_value(CommonTools.null2String(rest
						.getString("bond_value")));
				actionForm.setCustomer_name(CommonTools.null2String(rest
						.getString("customer_code")));
				actionForm.setArea(CommonTools.null2String(rest
						.getString("area")));
				actionForm.setSaleman_code(CommonTools.null2String(rest
						.getString("saleman_code")));
				actionForm.setPassing_date(CommonTools.null2String(CommonTools
						.getDateTime((rest.getDate("passing_date")))));
				actionForm.setArea_group(CommonTools.null2String(rest
						.getString("area_group")));
				actionForm.setOwn_production(CommonTools.null2String(rest
						.getString("own_production")));
				actionForm.setCustomer_type(CommonTools.null2String(rest
						.getString("customer_type")));
				actionForm.setProduct_type(CommonTools.null2String(rest
						.getString("product_type")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}
	}

	/**
	 * @编辑保存
	 */
	public boolean SAVE_UPDATE(F010102ActionForm actionForm,
			T_S_YHCZXXinfo t_YhczxxInfo) throws SelectException {
		boolean flag = true;
		List sqlList = new ArrayList();
		StringBuffer yhczsql = new StringBuffer();
		yhczsql = getInsZzxxSQL(t_YhczxxInfo);
		StringBuffer msql = new StringBuffer();
		msql.append("UPDATE c_orders SET ");
		msql.append("  manufacturing_order = '"
				+ actionForm.getManufacturing_order() + "',");
		msql.append("  signaure_date = '" + actionForm.getSignaure_date()
				+ "',");
		msql.append("  model = '" + actionForm.getModel() + "',");
		msql.append("  description = '" + actionForm.getModel() + "',");
		msql.append("  quantity = '" + actionForm.getQuantity() + "',");
		msql.append("  VAT = '" + actionForm.getVAT() + "',");
		msql.append("  unit_price = '" + actionForm.getUnit_price() + "',");
		msql.append("  total_price = '" + actionForm.getTotal_price() + "',");
		msql.append("  transport = '" + actionForm.getTransport() + "',");
		msql.append("  commission = '" + actionForm.getCommission() + "',");
		msql.append("  customer_delivery_date = '"
				+ actionForm.getCustomer_delivery_date() + "',");
		msql.append("  payment_terms = '" + actionForm.getPayment_terms()
				+ "',");
		msql.append("  bank_guaranty = '" + actionForm.getBank_guaranty()
				+ "',");
		msql.append("  bond_date = '" + actionForm.getBond_date() + "',");
		msql.append("  bond_value = '" + actionForm.getBond_value() + "',");
		msql.append("  customer_code = '" + actionForm.getCustomer_code()
				+ "',");
		msql.append("  area = '" + actionForm.getArea() + "',");
		msql.append("  saleman_code = '" + actionForm.getSaleman_code() + "',");
		msql.append("  passing_date = '" + actionForm.getPassing_date() + "',");
		msql.append("  area_group = '" + actionForm.getArea_group() + "',");
		msql.append("  own_production = '" + actionForm.getOwn_production()
				+ "',");
		msql.append("  remark = '" + actionForm.getRemark() + "',");
		msql.append("  customer_type = '" + actionForm.getCustomer_type()
				+ "',");
		msql.append("  product_type = '" + actionForm.getProduct_type() + "' ");
		msql.append("WHERE                                              	");
		msql.append("  manufacturing_order = '"
				+ CommonTools.null2String(actionForm
						.getOriginally_manufacturing_order().trim()) + "'");
		msql.append("and 																																																			");
		msql.append("  cotract_number = '"
				+ CommonTools
						.null2String(actionForm.getCotract_number().trim())
				+ "'					");
		try {
			log.info("订单信息管理 SAVE_UPDATE sql is " + msql.toString());
			sqlList.add(msql);
			sqlList.add(yhczsql);
			flag = super.executeUpdate(sqlList, true);
			actionForm.setErrorFlag("0");

		} catch (Exception e) {
			actionForm.setErrorFlag("1");
			e.printStackTrace();
			super.rollback();

		}
		super.commit();
		return flag;
	}

	/**
	 * @删除
	 */
	public boolean DELETE(F010102ActionForm actionForm,
			T_S_YHCZXXinfo t_YhczxxInfo, HttpServletRequest request)
			throws SelectException {
		boolean flag = true;
		List sqlList = new ArrayList();
		StringBuffer yhczsql = new StringBuffer();
		yhczsql = getInsZzxxSQL(t_YhczxxInfo);
		StringBuffer msql = new StringBuffer();
		msql.append(" update c_orders set state = '6' ");
		msql.append(" WHERE ");
		msql.append(" cotract_number = '"
				+ CommonTools.null2String(request.getParameter("id").trim())
				+ "' ");
		msql.append(" and  manufacturing_order = '"
				+ CommonTools.null2String(request.getParameter("id1").trim())
				+ "' ");
		try {
			log.info("订单信息管理 DELETE sql is " + msql.toString());
			sqlList.add(msql);
			sqlList.add(yhczsql);
			flag = super.executeUpdate(sqlList, true);
			actionForm.setErrorFlag("0");
		} catch (Exception e) {
			e.printStackTrace();
			super.rollback();
			actionForm.setErrorFlag("1");
		}
		super.commit();
		return flag;
	}

	/**
	 * @throws SQLException
	 * @以订单号码和工作好作为主键判断
	 * @cotract_number 订单号码
	 * @manufacturing_order 工作号
	 */
	public boolean Check_Cotract_Manufacturing_number(String manufacturing_order)
			throws SQLException {
		boolean falg = false;
		ResultSet rest = null;
		String sql = "select * from c_orders where manufacturing_order='"
				+ manufacturing_order + "'";
		try {
			log.info("工作好作为主键判断 sql is " + sql);
			rest = super.executeQuery(sql);
			falg = rest.next();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}
		return falg;
	}
}
